#!/bin/sh
#抽取navinfo_parquet_d00b维修事件ID2信息-蹄片状态数据到dws_hoof_status_pdi和dws_hoof_status_latest_pdi表中
#dws_hoof_status_pdi表针对每个蹄片,保存EBS百分数第一次小于阈值时的点数据；dws_hoof_status_latest_pdi表针对每个终端号,保存最新点数据
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
source ${UP_DIR}/system_env.sh
CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
source ${CUR_DIR}/config.properties

START_TIME=$(date +%s)

if [ -z "${hive_db}" ];then
  hive_db=dataplatform_trip
fi

if [ -z "${hive_db_source}" ];then
  hive_db_source=dongfeng
fi

if [ -z "$1" ];then
  part_time=$(date -d "0 days ago" +%Y%m%d)
else
  part_time=$1
fi

sql_suffix=$(cat <<EOF
INSERT OVERWRITE TABLE ${hive_db}.dws_hoof_status_latest_pdi PARTITION (day)
SELECT tid,
       frontLeftHoofStatus,
       frontRightHoofStatus,
       back1LeftHoofStatus,
       back1RightHoofStatus,
       back2LeftHoofStatus,
       back2RightHoofStatus,
       back3LeftHoofStatus,
       back3RightHoofStatus,
       '1' AS ebstype,
       CURRENT_TIMESTAMP() AS update_time,
       day
FROM (
  SELECT terminalid AS tid,
         SPLIT(maintenanceEvent2,'_')[4] AS frontLeftHoofStatus,
         SPLIT(maintenanceEvent2,'_')[5] AS frontRightHoofStatus,
         SPLIT(maintenanceEvent2,'_')[6] AS back1LeftHoofStatus,
         SPLIT(maintenanceEvent2,'_')[7] AS back1RightHoofStatus,
         SPLIT(maintenanceEvent2,'_')[8] AS back2LeftHoofStatus,
         SPLIT(maintenanceEvent2,'_')[9] AS back2RightHoofStatus,
         SPLIT(maintenanceEvent2,'_')[10] AS back3LeftHoofStatus,
         SPLIT(maintenanceEvent2,'_')[11] AS back3RightHoofStatus,
         part_time AS day,
         ROW_NUMBER() OVER (PARTITION BY terminalid ORDER BY gpsDate DESC) AS rn
  FROM ${hive_db_source}.navinfo_parquet_d00b
  WHERE part_time = ${part_time} ) t
WHERE t.rn = 1

UNION ALL
SELECT tid,
       frontLeftHoofStatus,
       frontRightHoofStatus,
       back1LeftHoofStatus,
       back1RightHoofStatus,
       back2LeftHoofStatus,
       back2RightHoofStatus,
       back3LeftHoofStatus,
       back3RightHoofStatus,
       '0' AS ebstype,
       CURRENT_TIMESTAMP() AS update_time,
       day
FROM (
  SELECT terminalid AS tid,
         SPLIT(maintenanceEvent2,'_')[12] AS frontLeftHoofStatus,
         SPLIT(maintenanceEvent2,'_')[13] AS frontRightHoofStatus,
         SPLIT(maintenanceEvent2,'_')[14] AS back1LeftHoofStatus,
         SPLIT(maintenanceEvent2,'_')[15] AS back1RightHoofStatus,
         SPLIT(maintenanceEvent2,'_')[16] AS back2LeftHoofStatus,
         SPLIT(maintenanceEvent2,'_')[17] AS back2RightHoofStatus,
         SPLIT(maintenanceEvent2,'_')[18] AS back3LeftHoofStatus,
         SPLIT(maintenanceEvent2,'_')[19] AS back3RightHoofStatus,
         part_time AS day,
         ROW_NUMBER() OVER (PARTITION BY terminalid ORDER BY gpsDate DESC) AS rn
  FROM ${hive_db_source}.navinfo_parquet_d00b
  WHERE part_time = ${part_time} ) t
WHERE t.rn = 1

EOF
)

hive_config=$(cat "${CUR_DIR}"/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入dws_hoof_status_latest_pdi的SQL为：${sql}"

#执行语句
hive -e "${sql}"
if [ $? -ne 0 ];then
  echo "导入dws_hoof_status_latest_pdi失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入dws_hoof_status_latest_pdi成功，耗时$((`date +%s`-START_TIME))秒..."
fi
